package com.alpha.schema;

import com.alpha.core.utils.StringUtil;
import com.alpha.schema.beans.*;
import com.google.common.collect.ImmutableList;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * Schema 对应 Sql 构造器
 *
 * @author chenruwan
 * @since 2022-01-01
 */
@Slf4j
public class SchemaSqlBuilder {
    private SchemaSqlBuilder() {
    }

    private static final String ADD_CONSTRAINT = "ADD CONSTRAINT";
    private static final String ADD_COLUMN = "ADD COLUMN";
    private static final String MODIFY = "MODIFY";
    private static final String TABLE_OPTIONS = "ENGINE=InnoDB DEFAULT CHARSET=utf8";

    public static List<String> buildAlterSql(Schema schema, Table table, Table current) {

        List<String> statements = new ArrayList<>();

        statements.addAll(buildFieldsAlterSql(table.getName(), table.getFields(), current.getFields()));

        statements.addAll(buildForeignersAlterSql(table, current));

        statements.addAll(buildRelationsAlterSql(schema, table, current));

        return statements;
    }

    public static List<String> buildCreateSql(Schema schema, Table table) {
        List<String> statements = new ArrayList<>();
        statements.add(buildTableCreateSql(table));

        for (Relation relation : table.getRelations()) {
            statements.addAll(buildRelationCreateSql(schema, table, relation));
        }
        return statements;
    }

    private static String alterStatement(String table, String op, String context) {
        return "ALTER TABLE `" + table + "` " + op + " " + context;
    }

    private static List<String> buildFieldsAlterSql(String table, List<Field> fields, List<Field> exists) {
        List<String> statements = new ArrayList<>();
        for (Field field : fields) {
            Optional<Field> optional = exists.stream().filter(f -> field.getName().equals(f.getName())).findFirst();
            if (optional.isEmpty()) {
                /* 新增字段 */
                statements.add(alterStatement(table, ADD_COLUMN, buildField(field)));
            } else if (field.isDifferentFrom(optional.get())) {
                /* 修改字段属性 */
                statements.add(alterStatement(table, MODIFY, buildField(field)));
            }
        }

        for (Field field : exists) {
            Optional<Field> optional = fields.stream().filter(f -> field.getName().equals(f.getName())).findFirst();
            if (optional.isEmpty() && Boolean.parseBoolean(field.getNotNull())) {
                /* 删除字段时，只修改字段的 not-null 属性 */
                field.setNotNull(null);
                statements.add(alterStatement(table, MODIFY, buildField(field)));
            }
        }
        return statements;
    }

    private static List<String> buildForeignersAlterSql(Table table, Table current) {
        List<String> statements = new ArrayList<>();
        for (Foreign foreign : current.getForeigners()) {
            if (table.getForeigners().stream().noneMatch(f -> foreign.getKey().equals(f.getKey()))) {
                /* 删除外键时，只删除外键约束，保留字段 */
                statements.add(alterStatement(table.getName(), "DROP FOREIGN KEY",
                        buildConstraintName(table.getName(), foreign.getKey())));
                statements.add(alterStatement(table.getName(), "DROP INDEX",
                        buildConstraintName(table.getName(), foreign.getKey())));
            }
        }

        for (Foreign foreign : table.getForeigners()) {
            Field field = new Field(foreign.getKey(), "bigint", foreign.getDesc())
                    .setNotNull(foreign.getNotNull()).setDefVal(foreign.getDefVal());

            Optional<Foreign> exist = current.getForeigners().stream()
                    .filter(f -> foreign.getKey().equals(f.getKey())).findFirst();
            if (exist.isEmpty()) {
                // 新增外键
                statements.add(alterStatement(table.getName(), ADD_COLUMN, buildField(field)));
                if (Boolean.parseBoolean(foreign.getConstrained())) {
                    // 添加外键约束
                    statements.add(alterStatement(table.getName(), ADD_CONSTRAINT,
                            buildConstraint(table.getName(), foreign.getKey(), foreign.getReferences())));
                }
                continue;
            }
            if (foreign.isDifferentFrom(exist.get())) {
                statements.add(alterStatement(table.getName(), MODIFY, buildField(field)));
            }
            // 添加外键约束
            if (isOnlyFirstTrue(foreign.getConstrained(), exist.get().getConstrained())) {
                statements.add(alterStatement(table.getName(), ADD_CONSTRAINT,
                        buildConstraint(table.getName(), foreign.getKey(), foreign.getReferences())));
            }
            // 删除外键约束
            if (isOnlyFirstTrue(exist.get().getConstrained(), foreign.getConstrained())) {
                statements.add(alterStatement(table.getName(), "DROP FOREIGN KEY",
                        buildConstraintName(table.getName(), foreign.getKey())));
                statements.add(alterStatement(table.getName(), "DROP INDEX",
                        buildConstraintName(table.getName(), foreign.getKey())));
            }
        }
        return statements;
    }

    private static boolean isOnlyFirstTrue(String first, String second) {
        return Boolean.parseBoolean(first) && !Boolean.parseBoolean(second);
    }

    private static List<String> buildRelationsAlterSql(Schema schema, Table table, Table current) {
        List<String> statements = new ArrayList<>();
        for (Relation relation : table.getRelations()) {
            Optional<Relation> optional = current.getRelations().stream()
                    .filter(r -> relation.getTo().equals(r.getTo())).findFirst();
            if (optional.isEmpty()) {
                // 新增关联表
                statements.addAll(buildRelationCreateSql(schema, table, relation));
                continue;
            }

            Relation exist = optional.get();

            // 暂不更新关联表的表名和两个外键的描述

            // 更新关联表外键约束
            Table toTable = schema.findTable(relation.getTo());
            if (Boolean.parseBoolean(relation.getConstrained()) && !Boolean.parseBoolean(exist.getConstrained())) {
                statements.add(alterStatement(relation.getName(), ADD_CONSTRAINT,
                        buildConstraint(relation.getName(), table.getSingular() + "_id", table.getName() + "(id)")));
                statements.add(alterStatement(relation.getName(), ADD_CONSTRAINT,
                        buildConstraint(relation.getName(), toTable.getSingular() + "_id", toTable.getName() + "(id)")));
            }

            // 更新关联表扩展字段
            statements.addAll(buildFieldsAlterSql(relation.getName(), relation.getFields(), exist.getFields()));
        }
        return statements;
    }

    private static List<String> buildRelationCreateSql(Schema schema, Table table, Relation relation) {
        Table toTable = schema.findTable(relation.getTo());

        List<Foreign> foreigners = ImmutableList.<Foreign>builder()
                .add(new Foreign().setKey(table.getSingular() + "_id")
                        .setReferences(table.getName() + "(id)")
                        .setDesc(table.getDesc() + " Id")
                        .setNotNull("true")
                        .setConstrained(relation.getConstrained()))
                .add(new Foreign().setKey(toTable.getSingular() + "_id")
                        .setReferences(toTable.getName() + "(id)")
                        .setDesc(toTable.getDesc() + " Id")
                        .setNotNull("true")
                        .setConstrained(relation.getConstrained()))
                .build();

        return List.of(buildTableCreateSql(new Table()
                .setName(relation.getName())
                .setDesc(relation.getDesc())
                .setFields(relation.getFields())
                .setForeigners(foreigners)));
    }

    private static String buildTableCreateSql(Table table) {
        StringBuilder builder = new StringBuilder();
        builder.append("CREATE TABLE `").append(table.getName()).append("` (");
        builder.append("id bigint NOT NULL COMMENT '主键 Id'");

        for (Field field : table.getFields()) {
            builder.append(", ").append(buildField(field));
        }

        for (Foreign foreign : table.getForeigners()) {
            builder.append(", ").append(buildField(new Field(foreign.getKey(), "bigint", foreign.getDesc())
                    .setNotNull(foreign.getNotNull()).setDefVal(foreign.getDefVal())));
        }
        builder.append(", updated_at datetime NOT NULL COMMENT '更新时间'");
        builder.append(", updated_by varchar(64) COMMENT '更新人'");
        builder.append(", created_at datetime NOT NULL COMMENT '创建时间'");
        builder.append(", created_by varchar(64) COMMENT '创建人'");
        builder.append(", deleted tinyint NOT NULL DEFAULT 0 COMMENT '是否已删除'");
        builder.append(", PRIMARY KEY(id)");
        for (Foreign foreign : table.getForeigners()) {
            if (Boolean.parseBoolean(foreign.getConstrained())) {
                builder.append(", CONSTRAINT ")
                        .append(buildConstraint(table.getName(), foreign.getKey(), foreign.getReferences()));
            }
        }
        builder.append(") ").append(TABLE_OPTIONS)
                .append(" COMMENT='").append(table.getDesc()).append("'");
        return builder.toString();
    }

    private static String buildField(Field field) {
        StringBuilder builder = new StringBuilder();
        builder.append("`").append(field.getName()).append("` ")
                .append(SchemaType.toSqlType(field.getType())).append(" ");
        if (Boolean.parseBoolean(field.getNotNull())) {
            builder.append("NOT NULL ");
        }
        if (field.getDefVal() != null) {
            builder.append("DEFAULT '").append(field.getDefVal()).append("' ");
        }
        if (field.getDesc() != null) {
            builder.append("COMMENT '").append(field.getDesc()).append("'");
        }
        return builder.toString();
    }

    private static String buildConstraint(String table, String key, String reference) {
        return StringUtil.format("{} FOREIGN KEY({}) REFERENCES {}",
                buildConstraintName(table, key), key, "`" + reference.replace("(", "`("));
    }

    private static String buildConstraintName(String table, String key) {
        return "fk_" + table + "_" + key;
    }
}
